package com.recive.excel;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.impl.Log4JLogger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;

import com.recive.util.StringUtil;

public class ExcelTool {

	private static XSSFWorkbook xssfWorkbook;

	private static Log4JLogger logger = new Log4JLogger();

	public static void main(String[] args) {
		try {
			// String qyval = "";
			// String khxmval = "";
			// String khdate = "";
			// String phjeval = "";
			// String freeval = "";
			// 使用list来存放数据
			List<String> qylist = new ArrayList<String>();
			List<String> khxmlist = new ArrayList<String>();
			List<String> khdatelist = new ArrayList<String>();
			List<String> phjelist = new ArrayList<String>();
			List<String> freelist = new ArrayList<String>();

			// 免费数据以及大理石数据处理
			List<String> azlist = new ArrayList<String>();
			List<String> balist = new ArrayList<String>();
			List<String> bhlist = new ArrayList<String>();
			List<String> bilist = new ArrayList<String>();

			InputStream is = new FileInputStream("data.xlsx");
			xssfWorkbook = new XSSFWorkbook(is);
			// 获取每一个工作薄
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				// 获取当前工作薄的每一行
				for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						// 读取第一列数据
						// XSSFCell one = xssfRow.getCell(0);
						// bid+=getValue(one)+",";
						// // 读取第二列数据
						// XSSFCell two = xssfRow.getCell(1);
						// phone+=getValue(two)+",";
						// XSSFCell three = xssfRow.getCell(2);
						// 读取第三列数据
						// 需要转换数据的话直接调用getValue获取字符串
						// 获取cx列的区域数据
						XSSFCell qy = xssfRow.getCell(101);
						// qyval += getValue(qy) + ",";
						qylist.add(getValue(qy));

						XSSFCell khxm = xssfRow.getCell(18);
						// khxmval += getValue(khxm) + ",";
						khxmlist.add(getValue(khxm));

						XSSFCell khyf = xssfRow.getCell(19);
						// khdate += getValue(khyf) + ",";
						khdatelist.add(getValue(khyf));

						XSSFCell phje = xssfRow.getCell(35);
						// phjeval += getValue(phje) + ",";
						phjelist.add(getValue(phje));

						XSSFCell free = xssfRow.getCell(35);
						// freeval += getValue(free) + ",";
						freelist.add(getValue(free));

						XSSFCell az = xssfRow.getCell(51);
						// freeval += getValue(free) + ",";
						azlist.add(getValue(az));

						XSSFCell ba = xssfRow.getCell(52);
						// freeval += getValue(free) + ",";
						balist.add(getValue(ba));

						XSSFCell bh = xssfRow.getCell(59);
						// freeval += getValue(free) + ",";
						bhlist.add(getValue(bh));

						XSSFCell bi = xssfRow.getCell(60);
						// freeval += getValue(free) + ",";
						bilist.add(getValue(bi));

					}
				}
				break;
			}
			// System.out.println(qylist);
			// System.out.println(khxmlist);
			// System.out.println(khdatelist);
			// System.out.println(phjelist);
			// System.out.println(azlist);
			// System.out.println(balist);
			// System.out.println(bhlist);
			// System.out.println(bilist);

			// 先统计出区域，客户姓名，考核日期三列基础数据
			Set<String> qySet = new HashSet<String>();
			for (int i = 1; i < qylist.size(); i++) {
				String cqy = qylist.get(i);
				if (!StringUtil.isEmpty(cqy)) {
					qySet.add(qylist.get(i));
				}
			}

			Set<String> khxmSet = new HashSet<String>();
			for (int i = 1; i < khxmlist.size(); i++) {
				String ckhxm = khxmlist.get(i);
				if (!StringUtil.isEmpty(ckhxm)) {
					khxmSet.add(khxmlist.get(i));
				}
			}

			Set<String> khdateSet = new HashSet<String>();
			for (int i = 1; i < khdatelist.size(); i++) {
				String cdate = khdatelist.get(i);
				if (!StringUtil.isEmpty(cdate)) {
					khdateSet.add(cdate);
				}
			}

			// 以三列基础数据为基础循环，做数据统计
			// 再做一次处理，将set转换成array,以便遍历的时候可以知道存储结果的数组下标，便于存储
			String[] arr_qy = qySet.toArray(new String[qySet.size()]);
			String[] arr_khxm = khxmSet.toArray(new String[khxmSet.size()]);
			String[] arr_khdate = khdateSet.toArray(new String[khdateSet.size()]);
			// 定义存储结果的数组
			int qysize = arr_qy.length;
			int khxmsize = arr_khxm.length;
			int khdatesize = arr_khdate.length;
			int size = qysize * khxmsize * khdatesize;
			double[] arr_phje = new double[size];// 配货金额
			double[] arr_mf = new double[size];// 免费
			double[] arr_dls = new double[size];// 大理石
			double[] arr_xshf = new double[size];// 先收后返
			double[] arr_xshfa = new double[size];// 先收后返a
			double[] arr_mdytb = new double[size];// 免单应退b
			for (int m = 0; m < arr_qy.length; m++) {
				for (int n = 0; n < arr_khxm.length; n++) {
					for (int q = 0; q < arr_khdate.length; q++) {// 遍历三列基础比较数据
						// 基础列比较值
						String baseqy = arr_qy[m];
						String basekhxm = arr_khxm[n];
						String basekhdate = arr_khdate[q];
						// 计算下标
						int index = m * khxmsize * khdatesize + n * khdatesize + q;
						arr_phje[index] = 0;
						arr_mf[index] = 0;
						arr_dls[index] = 0;
						arr_xshf[index] = 0;
						// 配货金额
						for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
							XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
							if (xssfSheet == null) {
								continue;
							}
							// 获取当前工作薄的每一行
							for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
								XSSFRow xssfRow = xssfSheet.getRow(rowNum);
								if (xssfRow != null) {
									XSSFCell qy = xssfRow.getCell(101);// 当前行的区域值
									String cqy = getValue(qy);

									XSSFCell khxm = xssfRow.getCell(18);// 当前行的客户姓名值
									String ckhxm = getValue(khxm);

									XSSFCell khyf = xssfRow.getCell(19);// 当前行的考核月份
									String ckhyf = getValue(khyf);

									if (baseqy.equals(cqy) && basekhxm.equals(ckhxm) && basekhdate.equals(ckhyf)) {
										XSSFCell phje = xssfRow.getCell(35);// 当前行的配货金额
										arr_phje[index] += Double.parseDouble(getValue(phje));
									
										XSSFCell ay = xssfRow.getCell(50);// 当前行的az列值
										XSSFCell az = xssfRow.getCell(51);// 当前行的az列值
										XSSFCell ba = xssfRow.getCell(52);// 当前行的ba列值
										XSSFCell bh = xssfRow.getCell(59);// 当前行的bh列值
										XSSFCell bi = xssfRow.getCell(60);// 当前行的bi列值
										
										//上述4列中含有关键字‘免’
										if(getValue(az).contains("免")||getValue(ba).contains("免")||getValue(bh).contains("免")||getValue(bi).contains("免")){
											arr_mf[index] += Double.parseDouble(getValue(phje));
										}
										//上述4列中不含有关键字免，但是含有关键字是‘石’
										if(isdls(ay, az, ba, bh, bi)){
											arr_dls[index] += Double.parseDouble(getValue(phje));
										}
//										if(!getValue(az).contains("免")&&!getValue(ba).contains("免")&&!getValue(bh).contains("免")&&!getValue(bi).contains("免")){
//											if(getValue(ay).contains("石")||getValue(az).contains("石")||getValue(ba).contains("石")||getValue(bh).contains("石")||getValue(bi).contains("石")){
//												arr_dls[index] += Double.parseDouble(getValue(phje));	
//											}
//										}
										
//										if(getValue(ay).contains("石")||getValue(az).contains("石")||getValue(ba).contains("石")||getValue(bh).contains("石")||getValue(bi).contains("石")){
//											arr_dls[index] += Double.parseDouble(getValue(phje));	
//										}
										
										//先收后返，配货-大理石-免费
										arr_xshf[index] = arr_phje[index] - arr_dls[index] - arr_mf[index];
										
										//先收後返a：e6 >= h6 ? h6 : e6
										
										//免单应退b：免費
										arr_mdytb[index] = arr_mf[index];
									}

								}
							}
							break;
						}
					}
				}
			}

			// System.out.println(arr_qy);
			// System.out.println(arr_khxm);
			// System.out.println(arr_khdate);
			// System.out.println(arr_phje);

			// 数据整合输出
			for (int a = 0; a < qysize; a++) {
				for (int b = 0; b < khxmsize; b++) {
					for (int c = 0; c < khdatesize; c++) {
						// 计算当前索引值
						int index = a * khxmsize * khdatesize + b * khdatesize + c;
						if (arr_phje[index] > 0) {
							System.out.println(
									arr_qy[a] + "  " + arr_khxm[b] + "  " + arr_khdate[c] + "  " + arr_phje[index]
											+"  "+arr_mf[index] + " 大理石： " +arr_dls[index]+"  " +arr_xshf[index]+"  "+arr_mdytb[index]);
						}
					}
				}
			}
			
			 //String[] titleRow = { "区域", "客户姓名", "考核月份", "茶电销量千分之三金额配货金额", "大理石/包件/自费", "先手后返（0.3%）", "先手后返金额a", "先收后返金额b", "应退配货费a+b", "备注" };
			 createExcel("result.xls", "统计", arr_qy,arr_khxm,arr_khdate,arr_phje,arr_mf,arr_dls,arr_xshf,arr_mdytb);

//			List<String> content = new ArrayList<String>();
//			content.add("123");
//			content.add("234");
//			writerFileContent("", "result.doc", content, "2");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 转换数据格式
	private static String getValue(XSSFCell xssfRow) {

		if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			// 时间类型被归类到了数字类型下面，需要二次判断
			short format = xssfRow.getCellStyle().getDataFormat();
			SimpleDateFormat sdf = null;
			if (format == 14 || format == 31 || format == 57 || format == 58) {
				// 日期
				// sdf = new SimpleDateFormat("yyyy-MM-dd");
				sdf = new SimpleDateFormat("yyyy-MM");
			} else if (format == 20 || format == 32) {
				// 时间
				sdf = new SimpleDateFormat("HH:mm");
			}
			double value = xssfRow.getNumericCellValue();
			Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);

			if (sdf == null) {
				return String.valueOf(xssfRow.getNumericCellValue());
			} else {
				return sdf.format(date);
			}

		} else if (xssfRow.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return String.valueOf(xssfRow.getDateCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	/**
	 * 创建新excel.
	 * 
	 * @param fileDir
	 *            excel的路径
	 * @param sheetName
	 *            要创建的表格索引
	 * @param titleRow
	 *            excel的第一行即表格头
	 */
	public static void createExcel(String fileDir, String sheetName, String[] arr_qy,String[] arr_khxm,String[] arr_khdate,
			double[] arr_phje,double[] arr_mf,double[] arr_dls,double[] arr_xshf,double[] arr_xshfb) throws Exception {
		// 创建workbook
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		HSSFSheet sheet1 = workbook.createSheet(sheetName);
		// 新建文件
		FileOutputStream out = null;
		try {
			// 添加表头
			HSSFSheet sheet = workbook.getSheet(sheetName);
//			for (short i = 0; i < titleRow.length; i++) {
//				HSSFCell cell = row.createCell(i);
//				cell.setCellValue(titleRow[i]);
//			}
			
			setTitles(sheet);
			
			setContent(sheet,arr_qy,arr_khxm,arr_khdate,arr_phje, arr_mf, arr_dls, arr_xshf,arr_xshfb);
			
			out = new FileOutputStream(fileDir);
			workbook.write(out);
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 写导入题库错误文件类容
	 * 
	 * @param fileDir
	 *            文件路径
	 * @param filename
	 *            文件名
	 * @param content
	 *            内容
	 * @param type
	 *            文件类型 1---excel 2 word;
	 * @author niugang
	 */
	private static void writerFileContent(String fileDir, String filename, List<String> content, String type) {
		XWPFDocument document = new XWPFDocument();
		OutputStream stream = null;
		BufferedOutputStream bufferStream = null;
		try {
			File dir = new File(fileDir);
			if (!dir.exists()) {
				dir.mkdirs();
			}
			stream = new FileOutputStream(new File(fileDir + filename));
			bufferStream = new BufferedOutputStream(stream, 1024);
			// 创建一个段落
			XWPFParagraph p1 = document.createParagraph();
			// 设置居中
			p1.setAlignment(ParagraphAlignment.CENTER);
			XWPFRun r1 = p1.createRun();
			// 是否加粗
			r1.setBold(true);
			// 与下一行的距离
			r1.setTextPosition(30);
			if ("2".equals(type)) {
				// 段落名称
				r1.setText("EXCEL导入题目错误格式详解");
			}
			if ("1".equals(type)) {
				// 段落名称
				r1.setText("WORD导入题目错误格式详解");
			}

			// 字体大小
			r1.setFontSize(18);// 字体大小
			// 增加换行
			r1.addCarriageReturn();
			// 创建第二个段落
			XWPFParagraph p2 = document.createParagraph();
			XWPFRun r2 = p2.createRun();
			for (int i = 0; i < content.size(); i++) {
				r2.setText(i + 1 + "、" + content.get(i));
				r2.addCarriageReturn();
			}
			// 设置字体
			r2.setFontFamily("仿宋");
			r2.setFontSize(14);// 字体大小
			document.write(stream);
			stream.close();
			bufferStream.close();
		} catch (Exception ex) {
			logger.info("写word或Excel错误文件失败：{}" + ex.getMessage());
		} finally {
			if (stream != null) {
				try {
					stream.close();
				} catch (IOException e) {
					e.printStackTrace();
					logger.info("写word或Excel错误文件失败：{}" + e.getMessage());
				}
			}
			if (bufferStream != null) {
				try {
					bufferStream.close();
				} catch (IOException e) {
					e.printStackTrace();
					logger.info("写word或Excel错误文件失败：{}" + e.getMessage());
				}
			}
		}

	}
	
	public static boolean isdls(XSSFCell ay,XSSFCell az,XSSFCell ba,XSSFCell bh,XSSFCell bi){
		
		String say = getValue(ay);
		String saz = getValue(az);
		String sba = getValue(ba);
		String sbh = getValue(bh);
		String sbi = getValue(bi);
		
		
		if(saz.contains("免")||sba.contains("免")||sbh.contains("免")||sbi.contains("免")){
			return false;
		}
		
		if(say.contains("石")||saz.contains("石")||sba.contains("石")||sbh.contains("石")||sbi.contains("石")){
			return true;
		}
		
		if(say.contains("包件")||saz.contains("包件")||sba.contains("包件")||sbh.contains("包件")||sbi.contains("包件")){
			return true;
		}
		
		String reg = "[0-9]-[0-9]";
		Pattern pattern = Pattern.compile(reg);
		Matcher mcay = pattern.matcher(say);
		while(mcay.find()){
		      return true;
		}
		Matcher mcaz = pattern.matcher(saz);
		while(mcaz.find()){
		      return true;
		}
		Matcher mcba = pattern.matcher(sba);
		while(mcba.find()){
		      return true;
		}
		Matcher mcbh = pattern.matcher(sbh);
		while(mcbh.find()){
		      return true;
		}
		Matcher mcbi = pattern.matcher(sbi);
		while(mcbi.find()){
		      return true;
		}
		return false;
	}

	public static void setTitles(HSSFSheet sheet){
		

		HSSFRow row = sheet.createRow(0); // 创建第一行
		HSSFRow row1 = sheet.createRow(1); // 创建第二行
		
		//"大理石/包件/自费", "先手后返（0.3%）", "先手后返金额a", "先收后返金额b", "应退配货费a+b", "备注";
		//标题行
		HSSFCell cell_qy = row.createCell(0);
		cell_qy.setCellValue("区域");

		HSSFCell cell_khxm = row.createCell(1);
		cell_khxm.setCellValue("客戶姓名");

		HSSFCell cell_khyf = row.createCell(2);
		cell_khyf.setCellValue("考核月份");

		HSSFCell cell_cdxl = row.createCell(3);
		cell_cdxl.setCellValue("茶电销量");
		
		HSSFCell cell_cdxl3 = row.createCell(4);
		cell_cdxl3.setCellValue("茶电销量千分之三金额配货金额");

		HSSFCell cell_phje = row.createCell(5);
		cell_phje.setCellValue("配货金额");
		
		HSSFCell cell_phmx = row.createCell(6);
		cell_phmx.setCellValue("配货明细");

		//第二行标题
		HSSFCell cell_dls = row1.createCell(6);
		cell_dls.setCellValue("大理石/包件/自费");
		
		HSSFCell cell_xshf = row1.createCell(7);
		cell_xshf.setCellValue("先收后返（0.3%）");
		
		HSSFCell cell_mf = row1.createCell(8);
		cell_mf.setCellValue("免费");
		
		//第二行标题

		HSSFCell cell_xhsfa = row.createCell(9);
		cell_xhsfa.setCellValue("先收后返a");

		HSSFCell cell_xshfb = row.createCell(10);
		cell_xshfb.setCellValue("免单应退金额b");

		HSSFCell cell_ytphf = row.createCell(11);
		cell_ytphf.setCellValue("应退配货费a+b");
	
		HSSFCell cell_bz = row.createCell(12);
		cell_bz.setCellValue("备注");

		//合并单元格
		Region region1 = new Region(0, (short)0, 1, (short)0);
		Region region2 = new Region(0, (short)1, 1, (short)1);
		Region region3 = new Region(0, (short)2, 1, (short)2);
		Region region4 = new Region(0, (short)3, 1, (short)3);
		Region region5 = new Region(0, (short)4, 1, (short)4);
		Region region6 = new Region(0, (short)5, 1, (short)5);
		Region region7 = new Region(0, (short)6, 0, (short)8);
		Region region8 = new Region(0, (short)9, 1, (short)9);
		Region region9 = new Region(0, (short)10, 1, (short)10);
		Region region10 = new Region(0, (short)11, 1, (short)11);
		Region region11 = new Region(0, (short)12, 1, (short)12);
		sheet.addMergedRegion(region1);
		sheet.addMergedRegion(region2);
		sheet.addMergedRegion(region3);
		sheet.addMergedRegion(region4);
		sheet.addMergedRegion(region5);
		sheet.addMergedRegion(region6);
		sheet.addMergedRegion(region7);
		sheet.addMergedRegion(region8);
		sheet.addMergedRegion(region9);
		sheet.addMergedRegion(region10);
		sheet.addMergedRegion(region11);
		
	}
	
	public static void setContent(HSSFSheet sheet,String[] arr_qy,String[] arr_khxm,String[] arr_khdate,
			double[] arr_phje,double[] arr_mf,double[] arr_dls,double[] arr_xshf,double[] arr_xshfb){
		int qysize = arr_qy.length;
		int khxmsize = arr_khxm.length;
		int khdatesize = arr_khdate.length;
		int nsize = 0;
		// 数据整合输出
		for (int a = 0; a < qysize; a++) {
			for (int b = 0; b < khxmsize; b++) {
				for (int c = 0; c < khdatesize; c++) {
					// 计算当前索引值
					int index = a * khxmsize * khdatesize + b * khdatesize + c;
					if(arr_phje[index] > 0){
						
						HSSFRow row = sheet.createRow(nsize+2);//标题占用了两行，所以从2开始						
						
						HSSFCell cell0 = row.createCell(0);
						cell0.setCellValue(arr_qy[a]);
						
						HSSFCell cell1 = row.createCell(1);
						cell1.setCellValue(arr_khxm[b]);
						
						HSSFCell cell2 = row.createCell(2);
						cell2.setCellValue(arr_khdate[c]);
						

						HSSFCell cell3 = row.createCell(3);
						cell3.setCellValue("");
						

						HSSFCell cell4 = row.createCell(4);
						cell4.setCellValue("");
						
						HSSFCell cell5 = row.createCell(5);
						cell5.setCellValue(arr_phje[index]);
						
						HSSFCell cell6 = row.createCell(6);
						cell6.setCellValue(arr_dls[index]);
						
						HSSFCell cell7 = row.createCell(7);
						cell7.setCellValue(arr_xshf[index]);
						
						HSSFCell cell8 = row.createCell(8);
						cell8.setCellValue(arr_mf[index]);
						
						HSSFCell cell9 = row.createCell(9);
						cell9.setCellValue("");
						
						HSSFCell cell10 = row.createCell(10);
						cell10.setCellValue(arr_xshfb[index]);
						
						HSSFCell cell11 = row.createCell(11);
						cell11.setCellValue("");
						
						HSSFCell cell12 = row.createCell(12);
						cell12.setCellValue("备注");
						
						nsize++;
					}
					
					if (arr_phje[index] > 0) {
						System.out.println(
								arr_qy[a] + "  " + arr_khxm[b] + "  " + arr_khdate[c] + "  " + arr_phje[index]
										+"  "+arr_mf[index] + " 大理石： " +arr_dls[index]+"  " +arr_xshf[index]+"  "+arr_xshfb[index]);
					}
				}
			}
		}
	}
	
}
